package cn.com.spbun.nddd.service;

import java.awt.Color;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.com.spbun.common.StringUtil;
import cn.com.spbun.nddd.model.ColumnInfo;
import cn.com.spbun.nddd.model.TableInfo;

/**
 * 将数据库表导出到Excel文件中。
 * 
 * @author NOLY DAKE
 *
 */
public class Export2ExcelService {

  /**
   * 导出数据库设计对象到Excel表中
   * 
   * @param tableInfos
   *          数据库表信心
   * @param exportFile
   *          要导出的文件
   */
  public void doExport(List<TableInfo> tableInfos, File exportFile) {

    XSSFWorkbook xwb = new XSSFWorkbook();

    // 导出数据库设计
    exportDBDesign(xwb, tableInfos);

    // 导出SQL
    exportSQL(xwb, tableInfos);

    try {
      xwb.write(new FileOutputStream(exportFile));
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }

    System.out.println("处理完成！");
  }

  private void exportSQL(XSSFWorkbook xwb, List<TableInfo> tableInfos) {

    XSSFSheet exportSheet = xwb.createSheet("SQL设计");

    int offRow = 0;

    for (TableInfo tableInfo : tableInfos) {

      exportSheet.createRow(offRow++);

      XSSFRow rowTableInfo = exportSheet.createRow(offRow++);
      XSSFCell cellTableInfo = rowTableInfo.createCell(1);
      cellTableInfo.setCellValue(tableInfo.getTableName().toUpperCase());

      XSSFRow rowSQL = exportSheet.createRow(offRow++);
      XSSFCell cellSQL = rowSQL.createCell(1);
      cellSQL.setCellValue(tableInfo.getSql());
    }
  }

  private void exportDBDesign(XSSFWorkbook xwb, List<TableInfo> tableInfos) {

    // 样式：标题的样式
    XSSFCellStyle titleStyle = createTitleStyle(xwb);
    // 样式：表格头的样式
    XSSFCellStyle tableHeaderStyle = createTableHeaderStyle(xwb);
    // 样式：表格内容的样式
    XSSFCellStyle tableContentStyle = createTableContentStyle(xwb);

    XSSFSheet exportSheet = xwb.createSheet("数据库设计");

    int offRow = 0;

    for (TableInfo tableInfo : tableInfos) {

      exportSheet.createRow(offRow++);
      // ------------------------------------------
      // &&&&&&&&&&&&&&&&&&&打印标题
      // ------------------------------------------
      XSSFRow rowTitle = exportSheet.createRow(offRow++);

      CellRangeAddress regionTitle = new CellRangeAddress(offRow - 1, offRow - 1, 1, 6); // 合并单元格
      exportSheet.addMergedRegion(regionTitle);// 合并单元格

      XSSFCell cellTitle = rowTitle.createCell(1);
      cellTitle.setCellValue(tableInfo.getTableName().toUpperCase());
      fillCellStyle(exportSheet, regionTitle, titleStyle);// 设置合并后的单元格格式

      // ------------------------------------------
      // &&&&&&&&&&&&&&&&&&&打印表名称
      // ------------------------------------------
      XSSFRow rowTableName = exportSheet.createRow(offRow++);
      CellRangeAddress regionTableName = new CellRangeAddress(offRow - 1, offRow - 1, 1, 6); // 合并单元格
      exportSheet.addMergedRegion(regionTableName); // 合并单元格

      XSSFCell cellTableName = rowTableName.createCell(1);

      cellTableName.setCellValue(
          "表名称：" + tableInfo.getTableName().toUpperCase() + "(" + tableInfo.getTableDescribe().toUpperCase() + ")");
      fillCellStyle(exportSheet, regionTableName, titleStyle);// 设置合并后的单元格格式

      List<ColumnInfo> columnInfos = tableInfo.getColumnInfos();

      // ------------------------------------------
      // &&&&&&&&&&&&&&&&&&&计算数据中的主键字段
      // ------------------------------------------
      List<ColumnInfo> pks = new ArrayList<ColumnInfo>();
      for (ColumnInfo columnInfo : columnInfos) {
        if (columnInfo.getIsPrimaryKey()) {
          pks.add(columnInfo);
        }
      }

      // ------------------------------------------
      // &&&&&&&&&&&&&&&&&&&打印表主键字段
      // ------------------------------------------
      XSSFRow rowPK = exportSheet.createRow(offRow++);

      CellRangeAddress regionPK = new CellRangeAddress(offRow - 1, offRow - 1, 1, 6); // 合并单元格
      exportSheet.addMergedRegion(regionPK); // 合并单元格

      XSSFCell cellPK = rowPK.createCell(1);

      if (pks.size() > 0) {

        StringBuffer sb = new StringBuffer();
        for (ColumnInfo columnInfo : pks) {
          if (sb.length() != 0) {
            sb.append(",");
          }
          sb.append(columnInfo.getColumnNameEn());
        }

        cellPK.setCellValue("表主键字段：" + sb.toString().toUpperCase());
      }
      fillCellStyle(exportSheet, regionPK, titleStyle);// 设置合并后的单元格格式

      // ------------------------------------------
      // &&&&&&&&&&&&&&&&&&&打印字段的标题
      // ------------------------------------------
      XSSFRow rowColumnTitle = exportSheet.createRow(offRow++);
      XSSFCell columnTitleCell = null;
      columnTitleCell = rowColumnTitle.createCell(1);
      columnTitleCell.setCellValue("序号");
      columnTitleCell.setCellStyle(tableHeaderStyle);

      columnTitleCell = rowColumnTitle.createCell(2);
      columnTitleCell.setCellValue("字段名称");
      columnTitleCell.setCellStyle(tableHeaderStyle);

      columnTitleCell = rowColumnTitle.createCell(3);
      columnTitleCell.setCellValue("字段类型");
      columnTitleCell.setCellStyle(tableHeaderStyle);

      columnTitleCell = rowColumnTitle.createCell(4);
      columnTitleCell.setCellValue("主键");
      columnTitleCell.setCellStyle(tableHeaderStyle);

      columnTitleCell = rowColumnTitle.createCell(5);
      columnTitleCell.setCellValue("非空");
      columnTitleCell.setCellStyle(tableHeaderStyle);

      columnTitleCell = rowColumnTitle.createCell(6);
      columnTitleCell.setCellValue("字段说明");
      columnTitleCell.setCellStyle(tableHeaderStyle);

      // ------------------------------------------
      // &&&&&&&&&&&&&&&&&&&打印字段
      // ------------------------------------------
      int i = 1;
      for (ColumnInfo columnInfo : columnInfos) {
        XSSFRow rowColumnInfo = exportSheet.createRow(offRow++);

        XSSFCell columnContentCell = null;

        columnContentCell = rowColumnInfo.createCell(1);
        columnContentCell.setCellValue(i);
        columnContentCell.setCellStyle(tableContentStyle);

        columnContentCell = rowColumnInfo.createCell(2);
        columnContentCell.setCellValue(columnInfo.getColumnNameEn().toUpperCase());
        columnContentCell.setCellStyle(tableContentStyle);

        columnContentCell = rowColumnInfo.createCell(3);
        if (!StringUtil.isEmpty(columnInfo.getColumnLength())) {
          columnContentCell.setCellValue(columnInfo.getColumnType() + "(" + columnInfo.getColumnLength() + ")");
        } else {
          columnContentCell.setCellValue(columnInfo.getColumnType());
        }
        columnContentCell.setCellStyle(tableContentStyle);

        columnContentCell = rowColumnInfo.createCell(4);
        columnContentCell.setCellValue(columnInfo.getIsPrimaryKey() ? "PRI" : "");
        columnContentCell.setCellStyle(tableContentStyle);

        columnContentCell = rowColumnInfo.createCell(5);
        columnContentCell.setCellValue(columnInfo.getIsCanNull() ? "NULL" : "NOT NULL");
        columnContentCell.setCellStyle(tableContentStyle);

        columnContentCell = rowColumnInfo.createCell(6);
        columnContentCell.setCellValue(columnInfo.getDescription());
        columnContentCell.setCellStyle(tableContentStyle);
        i++;
      }
    }
  }

  /**
   * 创建表格内容的样式
   * 
   * @param xwb
   * @return
   */
  private XSSFCellStyle createTableContentStyle(XSSFWorkbook xwb) {

    XSSFCellStyle style = xwb.createCellStyle();

    style.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
    style.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
    style.setBorderTop(CellStyle.BORDER_THIN);// 上边框
    style.setBorderRight(CellStyle.BORDER_THIN);// 右边框

    // 设置字体
    XSSFFont font = xwb.createFont();
    font.setFontName("宋体");
    font.setFontHeightInPoints((short) 10);// 设置字体大小

    style.setFont(font);// 选择需要用到的字体格式

    return style;
  }

  /**
   * 创建表格标题的样式
   * 
   * @param xwb
   * @return
   */
  private XSSFCellStyle createTableHeaderStyle(XSSFWorkbook xwb) {

    XSSFCellStyle style = xwb.createCellStyle();

    // 设置背景色
    XSSFColor color = new XSSFColor(new Color(230, 230, 230));

    style.setFillForegroundColor(color);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

    style.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
    style.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
    style.setBorderTop(CellStyle.BORDER_THIN);// 上边框
    style.setBorderRight(CellStyle.BORDER_THIN);// 右边框

    // 设置字体
    XSSFFont font = xwb.createFont();
    font.setFontName("宋体");
    font.setFontHeightInPoints((short) 10);// 设置字体大小
    font.setBold(true);

    style.setFont(font);// 选择需要用到的字体格式

    return style;
  }

  private void fillCellStyle(XSSFSheet sheet, CellRangeAddress region, XSSFCellStyle style) {

    for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
      XSSFRow row = sheet.getRow(i);
      if (region.getFirstColumn() != region.getLastColumn()) {
        for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
          XSSFCell cell = row.getCell((short) j);
          if (cell == null) {
            cell = row.createCell((short) j);
          }
          cell.setCellStyle(style);
        }
      }
    }
  }

  private XSSFCellStyle createTitleStyle(XSSFWorkbook xwb) {

    XSSFCellStyle style = xwb.createCellStyle();

    // 设置背景色
    XSSFColor color = new XSSFColor(new Color(230, 230, 230));

    style.setFillForegroundColor(color);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

    style.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
    style.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
    style.setBorderTop(CellStyle.BORDER_THIN);// 上边框
    style.setBorderRight(CellStyle.BORDER_THIN);// 右边框

    // 设置字体
    XSSFFont font = xwb.createFont();
    font.setFontName("Times New Roman");
    font.setFontHeightInPoints((short) 12);// 设置字体大小

    style.setFont(font);// 选择需要用到的字体格式

    return style;
  }
}
